package Model;

/*CREATE  TABLE IF NOT EXISTS `mydb`.`Registro` (
  `idRegistro` INT NULL AUTO_INCREMENT ,
  `data` DATETIME NULL ,
  `usuario` VARCHAR(45) NULL ,
  `tabela` VARCHAR(45) NULL ,
  `coluna` VARCHAR(45) NULL ,
  `valorAnterior` VARCHAR(999) NULL ,
  `valorCadstrado` VARCHAR(999) NULL ,
  `obs` VARCHAR(250) NULL ,
  PRIMARY KEY (`idRegistro`) )
ENGINE = InnoDB;*/
public class Registro {
    //<editor-fold defaultstate="collapsed" desc="Connection">
    private static com.mysql.jdbc.Connection connection;

    public static com.mysql.jdbc.Connection getConnection() {
        return connection;
    }

    public static void setConnection(com.mysql.jdbc.Connection connection) {
        Registro.connection = connection;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Variable Declaration">
    private int idRegistro;
    private java.util.Date data;
    private String usuario;
    private String tabela;
    private String coluna;
    private String valorAnterior;
    private String valorCadstrado;
    private String obs;
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Constructor">
    public Registro(int idRegistro) {
        try {
            com.mysql.jdbc.ResultSet rs = (com.mysql.jdbc.ResultSet) connection.createStatement().executeQuery("SELECT * FROM itens WHERE idRegistro = '" + idRegistro + "'");
            rs.next();
            this.idRegistro = rs.getInt("idRegistro");
            this.data = rs.getDate("data");
            this.usuario = rs.getString("usuario");
            this.tabela = rs.getString("tabela");
            this.coluna = rs.getString("coluna");
            this.valorAnterior = rs.getString("valorAnterior");
            this.valorCadstrado = rs.getString("valorCadstrado");
            this.obs = rs.getString("obs");
        } catch (Exception e) {
        }
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="toString">
    @Override
    public String toString() {
        String s="Registro:";
            s += "idRegistro=" + idRegistro;
        return s;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Getter">
    public int idRegistro() {
        return this.idRegistro;
    }

    public java.util.Date data() {
        return this.data;
    }

    public String usuario() {
        return this.usuario;
    }

    public String tabela() {
        return this.tabela;
    }

    public String coluna() {
        return this.coluna;
    }

    public String valorAnterior() {
        return this.valorAnterior;
    }

    public String valorCadstrado() {
        return this.valorCadstrado;
    }

    public String obs() {
        return this.obs;
    }

    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Setter">
    public boolean idRegistro(int idRegistro) {
        boolean ok = update("idRegistro", idRegistro);
        if (ok) {
            this.idRegistro = idRegistro;
        }
        return ok;
    }
    public boolean data(java.util.Date data) {
        boolean ok = update("data", data);
        if (ok) {
            this.data = data;
        }
        return ok;
    }
    public boolean usuario(String usuario) {
        boolean ok = update("usuario", usuario);
        if (ok) {
            this.usuario = usuario;
        }
        return ok;
    }
    public boolean tabela(String tabela) {
        boolean ok = update("tabela", tabela);
        if (ok) {
            this.tabela = tabela;
        }
        return ok;
    }
    public boolean coluna(String coluna) {
        boolean ok = update("coluna", coluna);
        if (ok) {
            this.coluna = coluna;
        }
        return ok;
    }
    public boolean valorAnterior(String valorAnterior) {
        boolean ok = update("valorAnterior", valorAnterior);
        if (ok) {
            this.valorAnterior = valorAnterior;
        }
        return ok;
    }
    public boolean valorCadstrado(String valorCadstrado) {
        boolean ok = update("valorCadstrado", valorCadstrado);
        if (ok) {
            this.valorCadstrado = valorCadstrado;
        }
        return ok;
    }
    public boolean obs(String obs) {
        boolean ok = update("obs", obs);
        if (ok) {
            this.obs = obs;
        }
        return ok;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Update">
    /**
     *
     * @param campo
     * @param valor
     * @return
     */
    public boolean update(String campo, Object valor) {
        try {
            String sql = "UPDATE Registro \n"
                    + "SET " + campo + " = '";
            if (valor instanceof String) {
                sql += (String) valor;
            } else if (valor instanceof Integer) {
                sql += (Integer) valor;
            } else if (valor instanceof java.sql.Timestamp) {
                sql += ((java.sql.Timestamp) valor).toString().split("\\.")[0];
            } else if (valor instanceof java.util.Date) {
                java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(
                        "yyyy-MM-dd hh:mm:ss");
                sql += sdf.format((java.util.Date) valor);
            } else {
                sql += valor.toString();
            }
            sql += "'\n WHERE \n idRegistro = '" + idRegistro + "'";
            com.mysql.jdbc.Statement st =
                    (com.mysql.jdbc.Statement) connection.createStatement();
            int x = st.executeUpdate(sql);
            if (x == 1) {
                return true;
            }
        } catch (Exception ex) {
        }
        return false;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Search">
    public static java.util.ArrayList<Registro> searchIdRegistro(int valor) {
        return search("idRegistro", valor+"");
    }
    public static java.util.ArrayList<Registro> searchData(java.util.Date valor) {
        return search("data", valor+"");
    }
    public static java.util.ArrayList<Registro> searchUsuario(String valor) {
        return search("usuario", valor+"");
    }
    public static java.util.ArrayList<Registro> searchTabela(String valor) {
        return search("tabela", valor+"");
    }
    public static java.util.ArrayList<Registro> searchColuna(String valor) {
        return search("coluna", valor+"");
    }
    public static java.util.ArrayList<Registro> searchValorAnterior(String valor) {
        return search("valorAnterior", valor+"");
    }
    public static java.util.ArrayList<Registro> searchValorCadstrado(String valor) {
        return search("valorCadstrado", valor+"");
    }
    public static java.util.ArrayList<Registro> searchObs(String valor) {
        return search("obs", valor+"");
    }

    public static java.util.ArrayList<Registro> search(String campo, String valor) {
        try {
            java.util.ArrayList<Registro> retorno = new java.util.ArrayList<>();
            String sql = "SELECT idRegistro FROM Registro "
                    + "WHERE " + campo + " LIKE '%" + valor + "%' "
                    + "ORDER BY " + campo;
            com.mysql.jdbc.ResultSet rs = (com.mysql.jdbc.ResultSet) connection.createStatement().executeQuery(sql);
            while (rs.next()) {
                retorno.add(new Registro(rs.getInt("idRegistro")));
            }
            return retorno;
        } catch (Exception e) {
        }
        return null;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="New">
    public static Registro newRegistro(int idRegistro, java.util.Date data, String usuario, String tabela, String coluna, String valorAnterior, String valorCadstrado, String obs){
            String sql = "INSERT INTO Registro (idRegistro, data, usuario, tabela, coluna, valorAnterior, valorCadstrado, obs)"
                    + " VALUES "
                    + "('"+idRegistro+"', '"+(new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format((java.util.Date) data)+"', '"+usuario+"', '"+tabela+"', '"+coluna+"', '"+valorAnterior+"', '"+valorCadstrado+"', '"+obs+"');";
        com.mysql.jdbc.Statement stmt = null;
        com.mysql.jdbc.ResultSet rs = null;
        try {
            stmt = (com.mysql.jdbc.Statement) connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                    java.sql.ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate(sql, com.mysql.jdbc.Statement.RETURN_GENERATED_KEYS);
            rs = (com.mysql.jdbc.ResultSet) stmt.getGeneratedKeys();
            if (rs.next()) {
                return new Registro(rs.getInt("idRegistro"));
            } else {
                return new Registro(idRegistro);
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    // ignore
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception ex) {
                    // ignore
                }
            }
        }
        return null;
    }
    //</editor-fold>
}